﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class GetUserInfoDAL
    {
#pragma warning disable CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        string Conn = ConfigurationSettings.AppSettings["con"].ToString();
#pragma warning restore CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        //修改密码
        public int Pwdchange(string cardid, string qpassword)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@cardid",cardid),
                new SqlParameter("@qpassword",qpassword)
            };
            string sqlstr = "update dbo.ryxx set password =@qpassword where rfkh =@cardid";
            return SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, sqlstr, sp);

        }
        //获取原始密码
        public DataSet getypassword(string cardid)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@cardid",cardid)
            };
            string sqlstr = "select password from ryxx where rfkh=@cardid";
            DataSet ds = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sqlstr, sp);
            return ds;
        }
        //根据openid查找用户信息
        public DataSet getUserInfo(string openid)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid)
            };
            string sqlstr = "select * from ryxx with(nolock) left join idbind with(nolock) on ryxx.ryid = idbind.ryid where idbind.openid = @openid  select isfeat from systemset";
            DataSet ds = SqlHelper.ExecuteDataSet(Conn,CommandType.Text,sqlstr,sp);
            return ds;
        }





        //根据openid查找用户的卡片状态
        public DataSet getUserInfos(string openid)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid)
            };
            string sqlstr = "select rfzt from ryxx where ryid=(select ryid from idbind where openid= @openid)";

            DataSet ds = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sqlstr, sp);
            return ds;
        }




        //查找充值赠送信息
        public DataSet getCZZSInfo(string openid)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid)
            };
            string sqlstr = "select * from czzs";
            DataSet ds = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sqlstr, sp);
            return ds;
        }
        

        //查找用户充值记录，根据openid，和时间范围
        public DataSet getRechargeCode(string openid,string startTime,string endTime)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid),
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            string sqlstr = "select convert(varchar,xfsj,120) as xfsj,cast(xfje as decimal(15,2)) xfje,xffs from lssj with(nolock) left join IdBind with(nolock) on lssj.ryid = IdBind.ryid where IdBind.openid = @openid and xfzl <> '消费' and xfsj between @startTime and @endTime order by xfsj desc";
            DataSet ds = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sqlstr, sp);
            return ds;
        }
        //查找用户消费记录
        public DataSet getConsumptionRecodes(string openid, string startTime, string endTime)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid),
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            string sqlstr = "select convert(varchar,xfsj,120) as xfsj,cast(xfje as decimal(15,2)) xfje,xffs from lssj with(nolock) left join IdBind with(nolock) on lssj.ryid = IdBind.ryid where IdBind.openid = @openid and xfzl = '消费' and xfsj between @startTime and @endTime order by xfsj desc";
            DataSet ds = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sqlstr, sp);
            return ds;
        }
        //获取增款总额
        public string getAddsCount(string openid, string startTime, string endTime)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid),
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            string sqlstr = "select sum(xfje) from lssj with(nolock) left join IdBind with(nolock) on lssj.ryid = IdBind.ryid where IdBind.openid = @openid and xfzl = '增款' and xfsj between @startTime and @endTime";
            string ds = SqlHelper.ExecuteScalar(Conn, CommandType.Text, sqlstr, sp).ToString();
            return ds;
        }
        //获取减款总额
        public string getDelsCount(string openid, string startTime, string endTime)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid),
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            string sqlstr = "select sum(xfje) from lssj with(nolock) left join IdBind with(nolock) on lssj.ryid = IdBind.ryid where IdBind.openid = @openid and xfzl = '减款' and xfsj between @startTime and @endTime";
            string ds = SqlHelper.ExecuteScalar(Conn, CommandType.Text, sqlstr, sp).ToString();
            return ds;
        }
        //获取消费总额
        public string getConsumptionCount(string openid, string startTime, string endTime)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid),
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            string sqlstr = "select isnull(sum(xfje),'') from lssj with(nolock) left join IdBind with(nolock) on lssj.ryid = IdBind.ryid where IdBind.openid = @openid and xfzl = '消费' and xfsj between @startTime and @endTime";
            string ds = SqlHelper.ExecuteScalar(Conn, CommandType.Text, sqlstr, sp).ToString();
            return ds;
        }
        //获取openid
        public string getOpenid(string UserID)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@UserID",UserID)
            };
            string sqlstr = "select openid from idbind where ryid = @UserID";
            string ds = SqlHelper.ExecuteScalar(Conn, CommandType.Text, sqlstr, sp).ToString();
            return ds;
        }

        /// <summary>
        /// 得到统计金额
        /// </summary>
        /// <param name="time1"></param>
        /// <param name="time2"></param>
        /// <returns></returns>
        public DataTable getjeCount(String time1, String time2)
        {
            SqlParameter[] par = new SqlParameter[] 
            {
                new SqlParameter("@time1",time1),
                new SqlParameter("@time2",time2)
            };
            StringBuilder SQLstring = new StringBuilder("select isnull(sum(case xffs when '微信充值' then cast(lssj.xfje as decimal(15,2)) end),0) as 微信充值,");
            SQLstring.Append("isnull(sum(case xffs when '现金交费' then cast(lssj.xfje as decimal(15,2)) end),0) as 现金交费,");
            SQLstring.Append("isnull(sum(case xffs when '单位补贴' then cast(lssj.xfje as decimal(15,2)) end),0) as 单位补贴,");
            SQLstring.Append("isnull(sum(case xffs when '开户押金' then cast(lssj.xfje as decimal(15,2)) end),0) as 开户押金,");
            SQLstring.Append("isnull(sum(case xffs when '换卡交费' then cast(lssj.xfje as decimal(15,2)) end),0) as 换卡交费,");
            SQLstring.Append("isnull(sum(case xffs when '加款机' then cast(lssj.xfje as decimal(15,2)) end),0) as 加款机,");
            SQLstring.Append("isnull(sum(case xffs when '充值机' then cast(lssj.xfje as decimal(15,2)) end),0) as 充值机,");
            SQLstring.Append("isnull(sum(case xffs when '补贴机' then cast(lssj.xfje as decimal(15,2)) end),0) as 补贴机,");
            SQLstring.Append("isnull(sum(case xffs when '现金赠送' then cast(lssj.xfje as decimal(15,2)) end),0) as 现金赠送,");
            SQLstring.Append("isnull(sum(case xffs when '单位补贴' then cast(lssj.xfje as decimal(15,2)) end),0) as 单位补贴,");
            SQLstring.Append("isnull(sum(case xffs when '补贴充值' then cast(lssj.xfje as decimal(15,2)) end),0) as 补贴充值,");
            SQLstring.Append("isnull(sum(case xffs when '补贴扣除' then cast(lssj.xfje as decimal(15,2)) end),0) as 补贴扣除,");
            SQLstring.Append("isnull(sum(case xffs when '现金提取' then cast(lssj.xfje as decimal(15,2)) end),0) as 现金提取,");
            SQLstring.Append("isnull(sum(case xffs when '退还押金' then cast(lssj.xfje as decimal(15,2)) end),0) as 退还押金,");
            SQLstring.Append("isnull(sum(case xffs when '退款机' then cast(lssj.xfje as decimal(15,2)) end),0) as 退款机,");
            SQLstring.Append("isnull(sum(case xffs when '退还余额' then cast(lssj.xfje as decimal(15,2)) end),0) as 退还余额,");
            SQLstring.Append("isnull(sum(case xffs when '因错退还' then cast(lssj.xfje as decimal(15,2)) end),0) as 因错退还,");
            SQLstring.Append("isnull(sum(case xffs when '餐费补扣' then cast(lssj.xfje as decimal(15,2)) end),0) as 餐费补扣,");
            SQLstring.Append("isnull(sum(case xffs when '正常消费' then cast(lssj.xfje as decimal(15,2)) end),0) as 正常消费,");
            SQLstring.Append("isnull(sum(case when xfzl = '减款' and xffs <> '餐费补扣' then cast(lssj.xfje as decimal(15,2)) end),0) as 减款,");
            SQLstring.Append("isnull(sum(case when xfzl = '增款' and xffs <> '因错退还' then cast(lssj.xfje as decimal(15,2)) end),0) as 增款,");
            SQLstring.Append("isnull(isnull(sum(case when xffs in ('餐费补扣','正常消费') then cast(lssj.xfje as decimal(15,2)) end),0) - sum(case when xffs = '因错退还' then cast(lssj.xfje as decimal(15,2)) end),0) as 实际消费,");
            SQLstring.Append("isnull(sum(case when xfzl = '增款' and xffs <> '因错退还' then cast(lssj.xfje as decimal(15,2)) end) - sum(case when xfzl = '减款' and xffs <> '餐费补扣' then cast(lssj.xfje as decimal(15,2)) end),0) as 实际收入 ");
            SQLstring.Append(" from lssj left join ryxx on lssj.ryid = ryxx.ryid ");
            if (!time1.Equals("") && !time2.Equals(""))
            {
                SQLstring.Append(" where xfsj >= @time1 and xfsj <= @time2 ");
            }
            return SqlHelper.ExecuteDataSet(@Conn, CommandType.Text, SQLstring.ToString(), par).Tables[0];

        }


        /// <summary>
        /// 改变卡状态，挂失，解挂。联机
        /// </summary>
        /// <param name="state">卡状态</param>
        /// <param name="cardNum">卡号</param>
        /// <returns></returns>
        public int changeCardState(string state, string cardNum)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@state",state),
                new SqlParameter("@cardid",cardNum)
            };
            string sqlstr = "update dbo.ryxx set rfzt=@state where rfkh=@cardid";
            return SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, sqlstr, sp);
            // string i = SqlHelper.ExecuteScalar(Conn, CommandType.StoredProcedure, "CardStateModify", sp).ToString();
            // return i;

        }

    }
}
